import sqlite3
import pymysql

mysql_conn = pymysql.connect(host="127.0.0.1", user="root", passwd="123456", db="xinhuizhilian", port=3306,
                            charset="utf8")
conn = sqlite3.connect('dianping_js.db')
c = conn.cursor()
# c.execute('''CREATE TABLE COMPANY
#        (ID INT PRIMARY KEY     NOT NULL,
#        NAME           TEXT    NOT NULL,
#        AGE            INT     NOT NULL,
#        ADDRESS        CHAR(50),
#        SALARY         REAL);''')
# c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
#       VALUES (1, 'Paul', 32, 'California', 20000.00 )");
#
# c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
#       VALUES (2, 'Allen', 25, 'Texas', 15000.00 )");
#
# c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
#       VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )");
#
# c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
#       VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )");

sql = 'select * from js_food '
crea_table = '''CREATE TABLE js_food (
              shop_name TEXT DEFAULT NULL,
              shop_per_capita      TEXT       DEFAULT NULL,
              shop_taste          TEXT       DEFAULT NULL,
              shop_environment    TEXT      DEFAULT NULL,
              shop_service        TEXT       DEFAULT NULL,
              shop_address        TEXT        DEFAULT NULL,
              shop_tel            TEXT        DEFAULT NULL,
              shop_job            TEXT        DEFAULT NULL,
              shop_summary       TEXT         DEFAULT NULL,
              page_url            TEXT          DEFAULT NULL
);'''

inser_sql = '''
INSERT INTO js_food VALUES (
 '宽窄巷子成都串串香', '人均：-', '口味：7.2', '环境：7.2', '服务：6.3', '安宜北路16号(体育馆向北150米)', '15262265133', '周一至周日\n11:00-14:00\n17:00-22:00', '暂无 ', 'http://www.dianping.com/shop/93607721');

'''
sel_sql = "select * from js_food"
# c.execute('''
# CREATE TABLE js(
# id int PRIMARY KEY ,
# name text DEFAULT null);
# ''')
c.execute(sel_sql)
date = c.fetchall()
print(len(date))
mysql_inset = "INSERT INTO js_food (shop_name,shop_per_capita ,shop_taste,shop_environment,shop_service,shop_address,shop_tel,shop_job,shop_summary,page_url)VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
cur = mysql_conn.cursor()
for i in date:
    print(i)
    cur.execute(mysql_inset,i)

mysql_conn.commit()
mysql_conn.close()
conn.commit()
conn.close()